/*
	EXEC SP_GENERATE_FORECAST_REPORT 'SALES'

THE SUPPORTED REPORT TYPES ARE:
	'SALES' 
	'OPERATIONS' 
	'salesForecast' 
	'salesForecastTemplate'
	'salesForecastTemplateGmChip'
*/
ALTER PROC SP_GENERATE_FORECAST_REPORT( @REPORT_TYPE VARCHAR(50) ) AS
BEGIN

CREATE TABLE #RESULTS_TABLE ( 
					PROJID INT,
					PRODID INT,
					Book VARCHAR(100),
					BU VARCHAR(100),
					PLM VARCHAR(100),
					Product_Segment VARCHAR(100),
					Product_Family VARCHAR(100),
					Chipset VARCHAR(100),
					Sales_Person VARCHAR(100),
					Region VARCHAR(100),
					Customer_Name VARCHAR(100),
					OEM VARCHAR(100),
					ODM VARCHAR(100),
					Carrier VARCHAR(100),
					Chipset_Part_Name VARCHAR(100),
					Chipset_Description VARCHAR(1000),
					Marketing_Part_Number VARCHAR(100),
					Manufacturing_Part_Number VARCHAR(100),
					Status VARCHAR(100),
					Chip_Type VARCHAR(100),
					Component_Description VARCHAR(100),
					Port_Count VARCHAR(100),
					chipset_component_count VARCHAR(100),
					package_size VARCHAR(100),
					Package_Type VARCHAR(100),
					Packing_Type VARCHAR(100),
					Packing_Quantity VARCHAR(100),
					Samples VARCHAR(100),
					Q1_Volume INT,
					Q2_Volume INT,
					Q3_Volume INT,
					Q4_Volume INT,
					Q5_Volume INT,
					Q1_ASP FLOAT,
					Q2_ASP FLOAT,
					Q3_ASP FLOAT,
					Q4_ASP FLOAT,
					Q5_ASP FLOAT,
					Q1_Gross_Revenue FLOAT,
					Q2_Gross_Revenue FLOAT,
					Q3_Gross_Revenue FLOAT,
					Q4_Gross_Revenue FLOAT,
					Q5_Gross_Revenue FLOAT,
					Q1_Rebates FLOAT,
					Q2_Rebates FLOAT,
					Q3_Rebates FLOAT,
					Q4_Rebates FLOAT,
					Q5_Rebates FLOAT,
					Q1_Net_Revenue VARCHAR(100),
					Q2_Net_Revenue VARCHAR(100),
					Q3_Net_Revenue VARCHAR(100),
					Q4_Net_Revenue VARCHAR(100),
					Q5_Net_Revenue VARCHAR(100),
					Q1_Material_Costs FLOAT,
					Q2_Material_Costs FLOAT,
					Q3_Material_Costs FLOAT,
					Q4_Material_Costs FLOAT,
					Q5_Material_Costs FLOAT,
					Q1_Royalty FLOAT,
					Q2_Royalty FLOAT,
					Q3_Royalty FLOAT,
					Q4_Royalty FLOAT,
					Q5_Royalty FLOAT,
					Q1_OH VARCHAR(100),
					Q2_OH VARCHAR(100),
					Q3_OH VARCHAR(100),
					Q4_OH VARCHAR(100),
					Q5_OH VARCHAR(100),
					Q1_COGS VARCHAR(100),
					Q2_COGS VARCHAR(100),
					Q3_COGS VARCHAR(100),
					Q4_COGS VARCHAR(100),
					Q5_COGS VARCHAR(100),
					Q1_GM VARCHAR(100),
					Q2_GM VARCHAR(100),
					Q3_GM VARCHAR(100),
					Q4_GM VARCHAR(100),
					Q5_GM VARCHAR(100),
					Q1_GM_PCTG VARCHAR(100),
					Q2_GM_PCTG VARCHAR(100),
					Q3_GM_PCTG VARCHAR(100),
					Q4_GM_PCTG VARCHAR(100),
					Q5_GM_PCTG VARCHAR(100),
					Q1_SHIPPED_TO_DATE VARCHAR(100),
					Q1_SHIPPED_TO_DATE_ASP VARCHAR(100),
					Q1_Scheduled_BKLG VARCHAR(100),
					Q2_Scheduled_BKLG VARCHAR(100),
					Q1_Scheduled_BKLG_ASP VARCHAR(100),
					Q2_Scheduled_BKLG_ASP VARCHAR(100),
					Unscheduled_BKLG VARCHAR(100),
					Unscheduled_ASP VARCHAR(100),
					UPDATED INT DEFAULT 1, --false
					Q1_PORT_VOLUME FLOAT,
					Q2_PORT_VOLUME FLOAT,
					Q3_PORT_VOLUME FLOAT,
					Q4_PORT_VOLUME FLOAT,
					Q5_PORT_VOLUME FLOAT
					 )	

DECLARE @CURR_QRT_START_DATE DATETIME
DECLARE @CURR_QRT_END_DATE DATETIME
SELECT @CURR_QRT_START_DATE = CQC_START_DATE, @CURR_QRT_END_DATE = CQC_END_DATE from Company_Quarterly_Calendar WHERE CURRENT_TIMESTAMP >= CQC_START_DATE AND CURRENT_TIMESTAMP <= CQC_END_DATE 	

INSERT INTO #RESULTS_TABLE (PROJID, PRODID, Book, BU, PLM, Product_Segment, Product_Family, Chipset, Sales_Person, Region, Customer_Name, OEM, ODM, Carrier, Chipset_Part_Name, Chipset_Description, Marketing_Part_Number, Manufacturing_Part_Number, Status, Chip_Type, Component_Description, Port_Count, chipset_component_count, package_size, Package_Type, Packing_Type, Packing_Quantity, Samples, Q1_SHIPPED_TO_DATE, Q1_Scheduled_BKLG,Q2_Scheduled_BKLG,Unscheduled_BKLG)
		SELECT PS.PROJID, PPR.PRODID, PPADT.Book, PPADT.BU, PPADT.PLM, PPADT.Product_Segment, PPADT.Product_Family, PPADT.Chipset, 
			(SELECT SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = PS.SPERSONID) AS Sales_Person,
			PPADT.Region, 
			(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = PS.CUSTID ) AS Customer_Name, 
			(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = PS.ENDUSERID ) AS [OEM], 
			(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = PS.ODMID ) AS ODM, PPADT.Carrier, PPADT.Chipset_Part_Name, PPADT.Chipset_Description, 
			(SELECT P_MARKETING_PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = PPR.PRODID) AS Marketing_Part_Number, 
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = PPR.PRODID) AS Manufacturing_Part_Number, 
			PS.PROJSTATUS, PPADT.Chip_Type, PPADT.Component_Description, PPADT.Port_Count, PPADT.chipset_component_count, PPADT.package_size, PPADT.Package_Type, PPADT.Packing_Type, PPADT.Packing_Quantity, PPADT.Samples,
			(SELECT SUM(CSLIT_Qty_SHIPPED) FROM Customer_Shipping_Line_Item_Table CSLIT, Customer_Open_Order_LineItem COOL, Sales_Order SO WHERE CSLIT.LineItem_ID = COOL.LineItem_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL.Product_ID = PPR.PRODID AND CSLIT_SHIPPED_DATE >= @CURR_QRT_START_DATE AND SO.Customer_ID = PS.CUSTID AND SO.SO_ODM_ID = PS.ENDUSERID ),
			(SELECT SUM(COOL_Qty) FROM Customer_Open_Order_LineItem COOL, Sales_Order SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL.Product_ID = PPR.PRODID AND COOL_SCHEDULE_DATE >= @CURR_QRT_START_DATE AND COOL_SCHEDULE_DATE <= @CURR_QRT_END_DATE AND SO.Customer_ID = PS.CUSTID AND SO.SO_ODM_ID = PS.ENDUSERID AND COOL.COOL_STATUS = 'APPROVED' ),
			(SELECT SUM(COOL_Qty) FROM Customer_Open_Order_LineItem COOL, Sales_Order SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL.Product_ID = PPR.PRODID AND COOL_SCHEDULE_DATE > @CURR_QRT_END_DATE AND COOL_SCHEDULE_DATE <= DATEADD( q, 1, @CURR_QRT_END_DATE) AND SO.Customer_ID = PS.CUSTID AND SO.SO_ODM_ID = PS.ENDUSERID AND COOL.COOL_STATUS = 'APPROVED' ),
			(SELECT SUM(COOL_Qty) FROM Customer_Open_Order_LineItem COOL, Sales_Order SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND COOL.Product_ID = PPR.PRODID AND COOL_SCHEDULE_DATE IS NULL AND SO.Customer_ID = PS.CUSTID AND SO.SO_ODM_ID = PS.ENDUSERID AND COOL.COOL_STATUS = 'APPROVED' )
			
			 FROM PROJSETUP PS
				JOIN PROJPRODREL PPR ON PS.PROJID = PPR.PROJID
				LEFT OUTER JOIN FCPROJPROD_ADDITIONAL_DATA_TABLE PPADT ON PPR.PROJID = PPADT.PROJID AND PPR.PRODID = PPADT.PRODID


DECLARE @CURR_QTR_NO INT
DECLARE @CURR_YEAR INT
DECLARE @TO_QTR_NO INT
DECLARE @TO_YEAR INT
DECLARE @CURR_DATE DATETIME
DECLARE @TMP DATETIME
DECLARE @PROJID INT
DECLARE @PRODID INT
DECLARE @QUARTER INT
DECLARE @YEAR INT
DECLARE @FC_QTY INT
DECLARE @FC_PRICE FLOAT
DECLARE @REBATE FLOAT
DECLARE @MATERIAL_COST FLOAT
DECLARE @ROYALTY FLOAT
DECLARE @PORT_VOLUME FLOAT
DECLARE @Q1_QTR_NO INT
DECLARE @Q2_QTR_NO INT
DECLARE @Q3_QTR_NO INT
DECLARE @Q4_QTR_NO INT
DECLARE @Q5_QTR_NO INT
DECLARE @Q1_YEAR INT
DECLARE @Q2_YEAR INT
DECLARE @Q3_YEAR INT
DECLARE @Q4_YEAR INT
DECLARE @Q5_YEAR INT
SET @CURR_DATE = CURRENT_TIMESTAMP

--SELECT @CURR_QTR_NO = datepart(q, @CURR_DATE)

SELECT @CURR_QTR_NO = CQC_Quarter_NO from Company_Quarterly_Calendar WHERE CONVERT(varchar(20), CURRENT_TIMESTAMP, 101 ) >= CQC_START_DATE AND CONVERT(varchar(20), CURRENT_TIMESTAMP, 101 ) <= CQC_END_DATE 	
SELECT @CURR_YEAR = datepart(yyyy, @CURR_DATE)
SET @Q1_QTR_NO = @CURR_QTR_NO
SET @Q1_YEAR = @CURR_YEAR

select @TMP =dateadd(q, 1, @CURR_DATE)
SELECT @Q2_QTR_NO = datepart(q, @TMP)
SELECT @Q2_YEAR = datepart(yyyy, @TMP)

select @TMP =dateadd(q, 2, @CURR_DATE)
SELECT @Q3_QTR_NO = datepart(q, @TMP)
SELECT @Q3_YEAR = datepart(yyyy, @TMP)

select @TMP =dateadd(q, 3, @CURR_DATE)
SELECT @Q4_QTR_NO = datepart(q, @TMP)
SELECT @Q4_YEAR = datepart(yyyy, @TMP)

select @TMP =dateadd(q, 4, @CURR_DATE)
SELECT @Q5_QTR_NO = datepart(q, @TMP)
SELECT @Q5_YEAR = datepart(yyyy, @TMP)
SELECT @TO_QTR_NO = @Q5_QTR_NO
SELECT @TO_YEAR = @Q5_YEAR
--PRINT '@TO_QTR_NO =' + CONVERT(VARCHAR,@TO_QTR_NO )
--PRINT '@TO_YEAR ='+CONVERT(VARCHAR, @TO_YEAR )

DECLARE @FC_CURSOR CURSOR
SET @FC_CURSOR = CURSOR FOR SELECT PROJID, PRODID, [QUARTER], [YEAR], QTY, PRICE FROM FCENTRYMASTER FCEM
									WHERE CAST(CONVERT(VARCHAR,FCEM.[YEAR])+CONVERT(VARCHAR,FCEM.[QUARTER]) AS INT)  >= CAST(CONVERT(VARCHAR,@CURR_YEAR)+CONVERT(VARCHAR,@CURR_QTR_NO) AS INT)
									AND CAST(CONVERT(VARCHAR,FCEM.[YEAR])+CONVERT(VARCHAR,FCEM.[QUARTER]) AS INT)  <= CAST(CONVERT(VARCHAR,@TO_YEAR)+CONVERT(VARCHAR,@TO_QTR_NO) AS INT)
OPEN @FC_CURSOR 
FETCH NEXT FROM @FC_CURSOR INTO @PROJID, @PRODID, @QUARTER, @YEAR, @FC_QTY, @FC_PRICE
WHILE (@@FETCH_STATUS = 0)
BEGIN
	IF( @QUARTER = @Q1_QTR_NO AND @YEAR = @Q1_YEAR )
		UPDATE #RESULTS_TABLE SET Q1_Volume = @FC_QTY, Q1_ASP = @FC_PRICE, Q1_Gross_Revenue = @FC_QTY*@FC_PRICE, UPDATED = 0
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q2_QTR_NO AND @YEAR = @Q2_YEAR )
		UPDATE #RESULTS_TABLE SET Q2_Volume = @FC_QTY, Q2_ASP = @FC_PRICE, Q2_Gross_Revenue = @FC_QTY*@FC_PRICE, UPDATED = 0
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q3_QTR_NO AND @YEAR = @Q3_YEAR )
		UPDATE #RESULTS_TABLE SET Q3_Volume = @FC_QTY, Q3_ASP = @FC_PRICE, Q3_Gross_Revenue = @FC_QTY*@FC_PRICE, UPDATED = 0
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q4_QTR_NO AND @YEAR = @Q4_YEAR )
		UPDATE #RESULTS_TABLE SET Q4_Volume = @FC_QTY, Q4_ASP = @FC_PRICE, Q4_Gross_Revenue = @FC_QTY*@FC_PRICE, UPDATED = 0
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q5_QTR_NO AND @YEAR = @Q5_YEAR )
		UPDATE #RESULTS_TABLE SET Q5_Volume = @FC_QTY, Q5_ASP = @FC_PRICE, Q5_Gross_Revenue = @FC_QTY*@FC_PRICE, UPDATED = 0
					WHERE PROJID = @PROJID AND PRODID = @PRODID

	FETCH NEXT FROM @FC_CURSOR INTO @PROJID, @PRODID, @QUARTER, @YEAR, @FC_QTY, @FC_PRICE	
END
CLOSE @FC_CURSOR
DEALLOCATE @FC_CURSOR

DECLARE @FC_ADDITIONAL_CURSOR CURSOR
SET @FC_ADDITIONAL_CURSOR = CURSOR FOR SELECT PROJID, PRODID, [QUARTER], [YEAR], REBATE, MATERIAL_COST, ROYALTY, PORT_VOLUME
									FROM [FCENTRYMASTER_ADDITIONAL_DATA_TABLE] FCAD
									WHERE CAST(CONVERT(VARCHAR,FCAD.[YEAR])+CONVERT(VARCHAR,FCAD.[QUARTER]) AS INT)  >= CAST(CONVERT(VARCHAR,@CURR_YEAR)+CONVERT(VARCHAR,@CURR_QTR_NO) AS INT)
									AND CAST(CONVERT(VARCHAR,FCAD.[YEAR])+CONVERT(VARCHAR,FCAD.[QUARTER]) AS INT)  <= CAST(CONVERT(VARCHAR,@TO_YEAR)+CONVERT(VARCHAR,@TO_QTR_NO) AS INT)
OPEN @FC_ADDITIONAL_CURSOR 
FETCH NEXT FROM @FC_ADDITIONAL_CURSOR INTO @PROJID, @PRODID, @QUARTER, @YEAR, @REBATE, @MATERIAL_COST, @ROYALTY, @PORT_VOLUME
WHILE (@@FETCH_STATUS = 0)
BEGIN
	IF( @QUARTER = @Q1_QTR_NO AND @YEAR = @Q1_YEAR )
		UPDATE #RESULTS_TABLE SET Q1_Rebates = @REBATE, Q1_Material_Costs = @MATERIAL_COST, Q1_Royalty = @ROYALTY, 
					Q1_Net_Revenue = Q1_Volume*(Q1_ASP-@REBATE), 
					Q1_OH = (@MATERIAL_COST+@ROYALTY)*0.055,
					Q1_COGS = @MATERIAL_COST+@ROYALTY+((@MATERIAL_COST+@ROYALTY)*0.055),
					Q1_GM = (Q1_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055),
					Q1_GM_PCTG = ( CASE WHEN Q1_ASP > 0 THEN (((Q1_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055))/(Q1_ASP-@REBATE)) ELSE 0 END),
					UPDATED = 0, Q1_PORT_VOLUME = @PORT_VOLUME
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q2_QTR_NO AND @YEAR = @Q2_YEAR )
		UPDATE #RESULTS_TABLE SET Q2_Rebates = @REBATE, Q2_Material_Costs = @MATERIAL_COST, Q2_Royalty = @ROYALTY, 
					Q2_Net_Revenue = Q2_Volume*(Q2_ASP-@REBATE), 
					Q2_OH = (@MATERIAL_COST+@ROYALTY)*0.055,
					Q2_COGS = @MATERIAL_COST+@ROYALTY+((@MATERIAL_COST+@ROYALTY)*0.055),
					Q2_GM = (Q2_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055),
					Q2_GM_PCTG = ( CASE WHEN Q2_ASP > 0 THEN (((Q2_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055))/(Q2_ASP-@REBATE)) ELSE 0 END),
					UPDATED = 0, Q2_PORT_VOLUME = @PORT_VOLUME
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q3_QTR_NO AND @YEAR = @Q3_YEAR )
		UPDATE #RESULTS_TABLE SET Q3_Rebates = @REBATE, Q3_Material_Costs = @MATERIAL_COST, Q3_Royalty = @ROYALTY, 
					Q3_Net_Revenue = Q3_Volume*(Q3_ASP-@REBATE), 
					Q3_OH = (@MATERIAL_COST+@ROYALTY)*0.055,
					Q3_COGS = @MATERIAL_COST+@ROYALTY+((@MATERIAL_COST+@ROYALTY)*0.055),
					Q3_GM = (Q3_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055),
					Q3_GM_PCTG = ( CASE WHEN Q3_ASP > 0 THEN (((Q3_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055))/(Q3_ASP-@REBATE)) ELSE 0 END),
					UPDATED = 0, Q3_PORT_VOLUME = @PORT_VOLUME
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q4_QTR_NO AND @YEAR = @Q4_YEAR )
		UPDATE #RESULTS_TABLE SET Q4_Rebates = @REBATE, Q4_Material_Costs = @MATERIAL_COST, Q4_Royalty = @ROYALTY, 
					Q4_Net_Revenue = Q2_Volume*(Q4_ASP-@REBATE), 
					Q4_OH = (@MATERIAL_COST+@ROYALTY)*0.055,
					Q4_COGS = @MATERIAL_COST+@ROYALTY+((@MATERIAL_COST+@ROYALTY)*0.055),
					Q4_GM = (Q4_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055),
					Q4_GM_PCTG = ( CASE WHEN Q4_ASP > 0 THEN (((Q4_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055))/(Q4_ASP-@REBATE)) ELSE 0 END),
					UPDATED = 0, Q4_PORT_VOLUME = @PORT_VOLUME
					WHERE PROJID = @PROJID AND PRODID = @PRODID
	ELSE IF( @QUARTER = @Q5_QTR_NO AND @YEAR = @Q5_YEAR )
		UPDATE #RESULTS_TABLE SET Q5_Rebates = @REBATE, Q5_Material_Costs = @MATERIAL_COST, Q5_Royalty = @ROYALTY, 
					Q5_Net_Revenue = Q5_Volume*(Q5_ASP-@REBATE), 
					Q5_OH = (@MATERIAL_COST+@ROYALTY)*0.055,
					Q5_COGS = @MATERIAL_COST+@ROYALTY+((@MATERIAL_COST+@ROYALTY)*0.055),
					Q5_GM = (Q5_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055),
					Q5_GM_PCTG = ( CASE WHEN Q5_ASP > 0 THEN (((Q5_ASP-@REBATE)-@MATERIAL_COST-@ROYALTY-((@MATERIAL_COST+@ROYALTY)*0.055))/(Q5_ASP-@REBATE)) ELSE 0 END),
					UPDATED = 0, Q5_PORT_VOLUME = @PORT_VOLUME
					WHERE PROJID = @PROJID AND PRODID = @PRODID

	FETCH NEXT FROM @FC_ADDITIONAL_CURSOR INTO @PROJID, @PRODID, @QUARTER, @YEAR, @REBATE, @MATERIAL_COST, @ROYALTY, @PORT_VOLUME	
END
CLOSE @FC_ADDITIONAL_CURSOR
DEALLOCATE @FC_ADDITIONAL_CURSOR


DECLARE @QUERY NVARCHAR(MAX)
DECLARE @Q1_LABEL VARCHAR(50), @Q2_LABEL VARCHAR(50), @Q3_LABEL VARCHAR(50), @Q4_LABEL VARCHAR(50), @Q5_LABEL VARCHAR(50)
DECLARE @Q1_VOLUME_LABEL VARCHAR(50), @Q2_VOLUME_LABEL VARCHAR(50), @Q3_VOLUME_LABEL VARCHAR(50), @Q4_VOLUME_LABEL VARCHAR(50), @Q5_VOLUME_LABEL VARCHAR(50)
DECLARE @Q1_ASP_LABEL VARCHAR(50), @Q2_ASP_LABEL VARCHAR(50), @Q3_ASP_LABEL VARCHAR(50), @Q4_ASP_LABEL VARCHAR(50), @Q5_ASP_LABEL VARCHAR(50)
DECLARE @Q1_GROSS_REVENUE_LABEL VARCHAR(50), @Q2_GROSS_REVENUE_LABEL VARCHAR(50), @Q3_GROSS_REVENUE_LABEL VARCHAR(50), @Q4_GROSS_REVENUE_LABEL VARCHAR(50), @Q5_GROSS_REVENUE_LABEL VARCHAR(50)
DECLARE @Q1_REBATES_LABEL VARCHAR(50), @Q2_REBATES_LABEL VARCHAR(50), @Q3_REBATES_LABEL VARCHAR(50), @Q4_REBATES_LABEL VARCHAR(50), @Q5_REBATES_LABEL VARCHAR(50)
DECLARE @Q1_NET_ASP_LABEL VARCHAR(50), @Q2_NET_ASP_LABEL VARCHAR(50), @Q3_NET_ASP_LABEL VARCHAR(50), @Q4_NET_ASP_LABEL VARCHAR(50), @Q5_NET_ASP_LABEL VARCHAR(50)
DECLARE @Q1_NET_REVENUE_LABEL VARCHAR(50), @Q2_NET_REVENUE_LABEL VARCHAR(50), @Q3_NET_REVENUE_LABEL VARCHAR(50), @Q4_NET_REVENUE_LABEL VARCHAR(50), @Q5_NET_REVENUE_LABEL VARCHAR(50)
DECLARE @Q1_MAT_COST_LABEL VARCHAR(50), @Q2_MAT_COST_LABEL VARCHAR(50), @Q3_MAT_COST_LABEL VARCHAR(50), @Q4_MAT_COST_LABEL VARCHAR(50), @Q5_MAT_COST_LABEL VARCHAR(50)
DECLARE @Q1_ROYALTY_LABEL VARCHAR(50), @Q2_ROYALTY_LABEL VARCHAR(50), @Q3_ROYALTY_LABEL VARCHAR(50), @Q4_ROYALTY_LABEL VARCHAR(50), @Q5_ROYALTY_LABEL VARCHAR(50)
DECLARE @Q1_OH_LABEL VARCHAR(50), @Q2_OH_LABEL VARCHAR(50), @Q3_OH_LABEL VARCHAR(50), @Q4_OH_LABEL VARCHAR(50), @Q5_OH_LABEL VARCHAR(50)
DECLARE @Q1_COGS_PER_UNIT_LABEL VARCHAR(50), @Q2_COGS_PER_UNIT_LABEL VARCHAR(50), @Q3_COGS_PER_UNIT_LABEL VARCHAR(50), @Q4_COGS_PER_UNIT_LABEL VARCHAR(50), @Q5_COGS_PER_UNIT_LABEL VARCHAR(50)
DECLARE @Q1_COGS_LABEL VARCHAR(50), @Q2_COGS_LABEL VARCHAR(50), @Q3_COGS_LABEL VARCHAR(50), @Q4_COGS_LABEL VARCHAR(50), @Q5_COGS_LABEL VARCHAR(50)
DECLARE @Q1_GM_LABEL VARCHAR(50), @Q2_GM_LABEL VARCHAR(50), @Q3_GM_LABEL VARCHAR(50), @Q4_GM_LABEL VARCHAR(50), @Q5_GM_LABEL VARCHAR(50)
DECLARE @Q1_GM_PER_LABEL VARCHAR(50), @Q2_GM_PER_LABEL VARCHAR(50), @Q3_GM_PER_LABEL VARCHAR(50), @Q4_GM_PER_LABEL VARCHAR(50), @Q5_GM_PER_LABEL VARCHAR(50)
DECLARE @Q1_NM_LABEL VARCHAR(50), @Q2_NM_LABEL VARCHAR(50), @Q3_NM_LABEL VARCHAR(50), @Q4_NM_LABEL VARCHAR(50), @Q5_NM_LABEL VARCHAR(50)
DECLARE @Q1_NM_PER_LABEL VARCHAR(50), @Q2_NM_PER_LABEL VARCHAR(50), @Q3_NM_PER_LABEL VARCHAR(50), @Q4_NM_PER_LABEL VARCHAR(50), @Q5_NM_PER_LABEL VARCHAR(50)
DECLARE @Q1_PORT_VOLUME_LABEL VARCHAR(50), @Q2_PORT_VOLUME_LABEL VARCHAR(50), @Q3_PORT_VOLUME_LABEL VARCHAR(50), @Q4_PORT_VOLUME_LABEL VARCHAR(50), @Q5_PORT_VOLUME_LABEL VARCHAR(50)
DECLARE @Q1_BKLG_VOLUME_LABEL VARCHAR(50), @Q2_BKLG_VOLUME_LABEL VARCHAR(50)
DECLARE @Q1_BKLG_ASP_LABEL VARCHAR(50), @Q2_BKLG_ASP_LABEL VARCHAR(50)
DECLARE @Q1_STD_VOLUME_LABEL VARCHAR(50), @Q1_STD_ASP_LABEL VARCHAR(50)


SET @Q1_LABEL = 'Q' + CONVERT(VARCHAR, @Q1_QTR_NO) + SUBSTRING(CONVERT(VARCHAR, @Q1_YEAR), 3, 2)
SET @Q2_LABEL = 'Q' + CONVERT(VARCHAR, @Q2_QTR_NO) + SUBSTRING(CONVERT(VARCHAR, @Q2_YEAR), 3, 2)
SET @Q3_LABEL = 'Q' + CONVERT(VARCHAR, @Q3_QTR_NO) + SUBSTRING(CONVERT(VARCHAR, @Q3_YEAR), 3, 2)
SET @Q4_LABEL = 'Q' + CONVERT(VARCHAR, @Q4_QTR_NO) + SUBSTRING(CONVERT(VARCHAR, @Q4_YEAR), 3, 2)
SET @Q5_LABEL = 'Q' + CONVERT(VARCHAR, @Q5_QTR_NO) + SUBSTRING(CONVERT(VARCHAR, @Q5_YEAR), 3, 2)

SET @Q1_VOLUME_LABEL = @Q1_LABEL + ' Volume' 
SET @Q2_VOLUME_LABEL = @Q2_LABEL + ' Volume' 
SET @Q3_VOLUME_LABEL = @Q3_LABEL + ' Volume' 
SET @Q4_VOLUME_LABEL = @Q4_LABEL + ' Volume' 
SET @Q5_VOLUME_LABEL = @Q5_LABEL + ' Volume' 

SET @Q1_ASP_LABEL = @Q1_LABEL + ' ASP' 
SET @Q2_ASP_LABEL = @Q2_LABEL + ' ASP' 
SET @Q3_ASP_LABEL = @Q3_LABEL + ' ASP' 
SET @Q4_ASP_LABEL = @Q4_LABEL + ' ASP' 
SET @Q5_ASP_LABEL = @Q5_LABEL + ' ASP' 

SET @Q1_GROSS_REVENUE_LABEL = @Q1_LABEL + ' Gross Revenue' 
SET @Q2_GROSS_REVENUE_LABEL = @Q2_LABEL + ' Gross Revenue' 
SET @Q3_GROSS_REVENUE_LABEL = @Q3_LABEL + ' Gross Revenue' 
SET @Q4_GROSS_REVENUE_LABEL = @Q4_LABEL + ' Gross Revenue' 
SET @Q5_GROSS_REVENUE_LABEL = @Q5_LABEL + ' Gross Revenue' 

SET @Q1_REBATES_LABEL = @Q1_LABEL + ' Rebates' 
SET @Q2_REBATES_LABEL = @Q2_LABEL + ' Rebates' 
SET @Q3_REBATES_LABEL = @Q3_LABEL + ' Rebates' 
SET @Q4_REBATES_LABEL = @Q4_LABEL + ' Rebates' 
SET @Q5_REBATES_LABEL = @Q5_LABEL + ' Rebates' 

SET @Q1_NET_ASP_LABEL = @Q1_LABEL + ' Net ASP' 
SET @Q2_NET_ASP_LABEL = @Q2_LABEL + ' Net ASP' 
SET @Q3_NET_ASP_LABEL = @Q3_LABEL + ' Net ASP' 
SET @Q4_NET_ASP_LABEL = @Q4_LABEL + ' Net ASP' 
SET @Q5_NET_ASP_LABEL = @Q5_LABEL + ' Net ASP' 

SET @Q1_NET_REVENUE_LABEL = @Q1_LABEL + ' Net Revenue' 
SET @Q2_NET_REVENUE_LABEL = @Q2_LABEL + ' Net Revenue' 
SET @Q3_NET_REVENUE_LABEL = @Q3_LABEL + ' Net Revenue' 
SET @Q4_NET_REVENUE_LABEL = @Q4_LABEL + ' Net Revenue' 
SET @Q5_NET_REVENUE_LABEL = @Q5_LABEL + ' Net Revenue' 

SET @Q1_MAT_COST_LABEL = @Q1_LABEL + ' Material Costs' 
SET @Q2_MAT_COST_LABEL = @Q2_LABEL + ' Material Costs' 
SET @Q3_MAT_COST_LABEL = @Q3_LABEL + ' Material Costs' 
SET @Q4_MAT_COST_LABEL = @Q4_LABEL + ' Material Costs' 
SET @Q5_MAT_COST_LABEL = @Q5_LABEL + ' Material Costs'

SET @Q1_ROYALTY_LABEL = @Q1_LABEL + ' Royalty' 
SET @Q2_ROYALTY_LABEL = @Q2_LABEL + ' Royalty' 
SET @Q3_ROYALTY_LABEL = @Q3_LABEL + ' Royalty' 
SET @Q4_ROYALTY_LABEL = @Q4_LABEL + ' Royalty' 
SET @Q5_ROYALTY_LABEL = @Q5_LABEL + ' Royalty'

SET @Q1_OH_LABEL = @Q1_LABEL + ' OH' 
SET @Q2_OH_LABEL = @Q2_LABEL + ' OH' 
SET @Q3_OH_LABEL = @Q3_LABEL + ' OH' 
SET @Q4_OH_LABEL = @Q4_LABEL + ' OH' 
SET @Q5_OH_LABEL = @Q5_LABEL + ' OH'

SET @Q1_COGS_PER_UNIT_LABEL = @Q1_LABEL + ' COGS/Unit' 
SET @Q2_COGS_PER_UNIT_LABEL = @Q2_LABEL + ' COGS/Unit' 
SET @Q3_COGS_PER_UNIT_LABEL = @Q3_LABEL + ' COGS/Unit' 
SET @Q4_COGS_PER_UNIT_LABEL = @Q4_LABEL + ' COGS/Unit' 
SET @Q5_COGS_PER_UNIT_LABEL = @Q5_LABEL + ' COGS/Unit'

SET @Q1_COGS_LABEL = @Q1_LABEL + ' COGS' 
SET @Q2_COGS_LABEL = @Q2_LABEL + ' COGS' 
SET @Q3_COGS_LABEL = @Q3_LABEL + ' COGS' 
SET @Q4_COGS_LABEL = @Q4_LABEL + ' COGS' 
SET @Q5_COGS_LABEL = @Q5_LABEL + ' COGS'

SET @Q1_GM_LABEL = @Q1_LABEL + ' GM' 
SET @Q2_GM_LABEL = @Q2_LABEL + ' GM' 
SET @Q3_GM_LABEL = @Q3_LABEL + ' GM' 
SET @Q4_GM_LABEL = @Q4_LABEL + ' GM' 
SET @Q5_GM_LABEL = @Q5_LABEL + ' GM'

SET @Q1_GM_PER_LABEL = @Q1_LABEL + ' GM%' 
SET @Q2_GM_PER_LABEL = @Q2_LABEL + ' GM%' 
SET @Q3_GM_PER_LABEL = @Q3_LABEL + ' GM%' 
SET @Q4_GM_PER_LABEL = @Q4_LABEL + ' GM%' 
SET @Q5_GM_PER_LABEL = @Q5_LABEL + ' GM%'

SET @Q1_NM_LABEL = @Q1_LABEL + ' NM' 
SET @Q2_NM_LABEL = @Q2_LABEL + ' NM' 
SET @Q3_NM_LABEL = @Q3_LABEL + ' NM' 
SET @Q4_NM_LABEL = @Q4_LABEL + ' NM' 
SET @Q5_NM_LABEL = @Q5_LABEL + ' NM'


print @Q1_NM_LABEL
SET @Q1_NM_PER_LABEL = @Q1_LABEL + ' NM%' 
SET @Q2_NM_PER_LABEL = @Q2_LABEL + ' NM%' 
SET @Q3_NM_PER_LABEL = @Q3_LABEL + ' NM%' 
SET @Q4_NM_PER_LABEL = @Q4_LABEL + ' NM%' 
SET @Q5_NM_PER_LABEL = @Q5_LABEL + ' NM%'

SET @Q1_PORT_VOLUME_LABEL = @Q1_LABEL + ' Port Volume' 
SET @Q2_PORT_VOLUME_LABEL = @Q2_LABEL + ' Port Volume' 
SET @Q3_PORT_VOLUME_LABEL = @Q3_LABEL + ' Port Volume' 
SET @Q4_PORT_VOLUME_LABEL = @Q4_LABEL + ' Port Volume'
SET @Q5_PORT_VOLUME_LABEL = @Q5_LABEL + ' Port Volume'


SET @Q1_BKLG_VOLUME_LABEL = @Q1_LABEL + ' BKLG Volume' 
SET @Q2_BKLG_VOLUME_LABEL = @Q2_LABEL + ' BKLG Volume' 
SET @Q1_BKLG_ASP_LABEL = @Q1_LABEL + ' BKLG ASP'
SET @Q2_BKLG_ASP_LABEL = @Q2_LABEL + ' BKLG ASP'
SET @Q1_STD_VOLUME_LABEL = @Q1_LABEL + ' STD Volume'
SET @Q1_STD_ASP_LABEL = @Q1_LABEL + ' STD ASP'

IF(@REPORT_TYPE = 'salesForecast' OR @REPORT_TYPE = 'salesForecastTemplate')
BEGIN
	SET @QUERY = N'SELECT Book AS [Book],
		BU AS [BU],
		PLM AS [PLM],
		Product_Segment AS [Product Segment],
		Product_Family AS [Product Family],
		Chipset AS [Chipset],
		Sales_Person AS [Sales Person],
		Region AS [Region],
		Customer_Name AS [Customer Name],
		OEM AS [OEM] ,
		ODM AS [ODM],
		Carrier AS [Carrier],
		Chipset_Part_Name AS [Chipset Part Name],
		Chipset_Description AS [Chipset Description],
		Marketing_Part_Number AS [Marketing Part Number],
		Manufacturing_Part_Number AS [Manufacturing Part Number],
		Status AS [Status],
		Chip_Type AS [Chip Type],
		Component_Description AS [Component Description],
		Port_Count AS [Port Count],
		chipset_component_count AS [chipset component count],
		package_size AS [package size],
		Package_Type AS [Package Type],
		Packing_Type AS [Packing Type],
		Packing_Quantity AS [Packing Quantity],
		Samples AS [Samples],
		Q1_Volume AS [' + @Q1_VOLUME_LABEL + '],
		Q2_Volume AS [' + @Q2_VOLUME_LABEL + '],
		Q3_Volume AS [' + @Q3_VOLUME_LABEL + '],
		Q4_Volume AS [' + @Q4_VOLUME_LABEL + '],
		Q5_Volume AS [' + @Q5_VOLUME_LABEL + '],
		Q1_ASP AS [' + @Q1_ASP_LABEL + '],
		Q2_ASP AS [' + @Q2_ASP_LABEL + '],
		Q3_ASP AS [' + @Q3_ASP_LABEL + '],
		Q4_ASP AS [' + @Q4_ASP_LABEL + '],
		Q5_ASP AS [' + @Q5_ASP_LABEL + '],
		'+'''${'+@Q1_VOLUME_LABEL+'}*${'+@Q1_ASP_LABEL+'}'' AS [' + @Q1_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q2_VOLUME_LABEL+'}*${'+@Q2_ASP_LABEL+'}'' AS [' + @Q2_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q3_VOLUME_LABEL+'}*${'+@Q3_ASP_LABEL+'}'' AS [' + @Q3_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q4_VOLUME_LABEL+'}*${'+@Q4_ASP_LABEL+'}'' AS [' + @Q4_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q5_VOLUME_LABEL+'}*${'+@Q5_ASP_LABEL+'}'' AS [' + @Q5_GROSS_REVENUE_LABEL + '],
		Q1_Rebates AS [' + @Q1_REBATES_LABEL + '],
		Q2_Rebates AS [' + @Q2_REBATES_LABEL + '],
		Q3_Rebates AS [' + @Q3_REBATES_LABEL + '],
		Q4_Rebates AS [' + @Q4_REBATES_LABEL + '],
		Q5_Rebates AS [' + @Q5_REBATES_LABEL + '],
		
		'+'''${'+@Q1_ASP_LABEL+'}-${'+@Q1_REBATES_LABEL+'}'' AS [' + @Q1_NET_ASP_LABEL + '],
		'+'''${'+@Q2_ASP_LABEL+'}-${'+@Q2_REBATES_LABEL+'}'' AS [' + @Q2_NET_ASP_LABEL + '],
		'+'''${'+@Q3_ASP_LABEL+'}-${'+@Q3_REBATES_LABEL+'}'' AS [' + @Q3_NET_ASP_LABEL + '],
		'+'''${'+@Q4_ASP_LABEL+'}-${'+@Q4_REBATES_LABEL+'}'' AS [' + @Q4_NET_ASP_LABEL + '],
		'+'''${'+@Q5_ASP_LABEL+'}-${'+@Q5_REBATES_LABEL+'}'' AS [' + @Q5_NET_ASP_LABEL + '],

		'+'''${'+@Q1_VOLUME_LABEL+'}*${'+@Q1_NET_ASP_LABEL+'}'' AS [' + @Q1_NET_REVENUE_LABEL + '],
		'+'''${'+@Q2_VOLUME_LABEL+'}*${'+@Q2_NET_ASP_LABEL+'}'' AS [' + @Q2_NET_REVENUE_LABEL + '],
		'+'''${'+@Q3_VOLUME_LABEL+'}*${'+@Q3_NET_ASP_LABEL+'}'' AS [' + @Q3_NET_REVENUE_LABEL + '],
		'+'''${'+@Q4_VOLUME_LABEL+'}*${'+@Q4_NET_ASP_LABEL+'}'' AS [' + @Q4_NET_REVENUE_LABEL + '],		
		'+'''${'+@Q5_VOLUME_LABEL+'}*${'+@Q5_NET_ASP_LABEL+'}'' AS [' + @Q5_NET_REVENUE_LABEL + ']
		'
	IF(@REPORT_TYPE = 'salesForecastTemplate')
	BEGIN
		SET @QUERY = @QUERY + N', Q1_Scheduled_BKLG AS [' + @Q1_BKLG_VOLUME_LABEL + '],
						Q2_Scheduled_BKLG AS [' + @Q2_BKLG_VOLUME_LABEL + '],
						Q1_Scheduled_BKLG_ASP AS [' + @Q1_BKLG_ASP_LABEL + '],
						Q2_Scheduled_BKLG_ASP AS [' + @Q2_BKLG_ASP_LABEL + '],
						Q1_SHIPPED_TO_DATE AS [' + @Q1_STD_VOLUME_LABEL + '],
						Q1_SHIPPED_TO_DATE_ASP AS [' + @Q1_STD_ASP_LABEL + '],
						Unscheduled_BKLG AS [' + 'Unscheduled Bklg' + '],
						Unscheduled_ASP AS [' + 'Unscheduled ASP' + ']						
					'
	END
	ELSE
	BEGIN
		SET @QUERY = @QUERY + N', Q1_Material_Costs AS [' + @Q1_MAT_COST_LABEL + '],
			Q2_Material_Costs AS [' + @Q2_MAT_COST_LABEL + '],
			Q3_Material_Costs AS [' + @Q3_MAT_COST_LABEL + '],
			Q4_Material_Costs AS [' + @Q4_MAT_COST_LABEL + '],
			Q5_Material_Costs AS [' + @Q5_MAT_COST_LABEL + '],
			Q1_Royalty AS [' + @Q1_ROYALTY_LABEL + '],
			Q2_Royalty AS [' + @Q2_ROYALTY_LABEL + '],
			Q3_Royalty AS [' + @Q3_ROYALTY_LABEL + '],
			Q4_Royalty AS [' + @Q4_ROYALTY_LABEL + '],
			Q5_Royalty AS [' + @Q5_ROYALTY_LABEL + '],

			'+'''(${'+@Q1_MAT_COST_LABEL+'}+${'+@Q1_ROYALTY_LABEL+'})*0.05'' AS [' + @Q1_OH_LABEL + '],
			'+'''(${'+@Q2_MAT_COST_LABEL+'}+${'+@Q2_ROYALTY_LABEL+'})*0.05'' AS [' + @Q2_OH_LABEL + '],
			'+'''(${'+@Q3_MAT_COST_LABEL+'}+${'+@Q3_ROYALTY_LABEL+'})*0.05'' AS [' + @Q3_OH_LABEL + '],
			'+'''(${'+@Q4_MAT_COST_LABEL+'}+${'+@Q4_ROYALTY_LABEL+'})*0.05'' AS [' + @Q4_OH_LABEL + '],
			'+'''(${'+@Q5_MAT_COST_LABEL+'}+${'+@Q5_ROYALTY_LABEL+'})*0.05'' AS [' + @Q5_OH_LABEL + '],
			
			'+'''(${'+@Q1_MAT_COST_LABEL+'}+${'+@Q1_ROYALTY_LABEL+'}+${'+ @Q1_OH_LABEL +'})'' AS [' + @Q1_COGS_PER_UNIT_LABEL + '],
			'+'''(${'+@Q2_MAT_COST_LABEL+'}+${'+@Q2_ROYALTY_LABEL+'}+${'+ @Q2_OH_LABEL +'})'' AS [' + @Q2_COGS_PER_UNIT_LABEL + '],
			'+'''(${'+@Q3_MAT_COST_LABEL+'}+${'+@Q3_ROYALTY_LABEL+'}+${'+ @Q3_OH_LABEL +'})'' AS [' + @Q3_COGS_PER_UNIT_LABEL + '],
			'+'''(${'+@Q4_MAT_COST_LABEL+'}+${'+@Q4_ROYALTY_LABEL+'}+${'+ @Q4_OH_LABEL +'})'' AS [' + @Q4_COGS_PER_UNIT_LABEL + '],	
			'+'''(${'+@Q5_MAT_COST_LABEL+'}+${'+@Q5_ROYALTY_LABEL+'}+${'+ @Q5_OH_LABEL +'})'' AS [' + @Q5_COGS_PER_UNIT_LABEL + '],	

			'+'''${'+@Q1_COGS_PER_UNIT_LABEL+'}*${'+@Q1_VOLUME_LABEL+'}'' AS [' + @Q1_COGS_LABEL + '],
			'+'''${'+@Q2_COGS_PER_UNIT_LABEL+'}*${'+@Q2_VOLUME_LABEL+'}'' AS [' + @Q2_COGS_LABEL + '],
			'+'''${'+@Q3_COGS_PER_UNIT_LABEL+'}*${'+@Q3_VOLUME_LABEL+'}'' AS [' + @Q3_COGS_LABEL + '],
			'+'''${'+@Q4_COGS_PER_UNIT_LABEL+'}*${'+@Q4_VOLUME_LABEL+'}'' AS [' + @Q4_COGS_LABEL + '],
			'+'''${'+@Q5_COGS_PER_UNIT_LABEL+'}*${'+@Q5_VOLUME_LABEL+'}'' AS [' + @Q5_COGS_LABEL + '],

			'+'''${'+@Q1_GROSS_REVENUE_LABEL+'}-${'+@Q1_COGS_LABEL+'}'' AS [' + @Q1_GM_LABEL + '],
			'+'''${'+@Q2_GROSS_REVENUE_LABEL+'}-${'+@Q2_COGS_LABEL+'}'' AS [' + @Q2_GM_LABEL + '],
			'+'''${'+@Q3_GROSS_REVENUE_LABEL+'}-${'+@Q3_COGS_LABEL+'}'' AS [' + @Q3_GM_LABEL + '],
			'+'''${'+@Q4_GROSS_REVENUE_LABEL+'}-${'+@Q4_COGS_LABEL+'}'' AS [' + @Q4_GM_LABEL + '],
			'+'''${'+@Q5_GROSS_REVENUE_LABEL+'}-${'+@Q5_COGS_LABEL+'}'' AS [' + @Q5_GM_LABEL + '],

			'+'''IF(ISERROR(${'+ @Q1_GM_LABEL+'}/${'+@Q1_GROSS_REVENUE_LABEL+'}),0,(${'+@Q1_GM_LABEL+'}/${'+@Q1_GROSS_REVENUE_LABEL+'}))'' AS [' + @Q1_GM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+ @Q2_GM_LABEL+'}/${'+@Q2_GROSS_REVENUE_LABEL+'}),0,(${'+@Q2_GM_LABEL+'}/${'+@Q2_GROSS_REVENUE_LABEL+'}))'' AS [' + @Q2_GM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+ @Q3_GM_LABEL+'}/${'+@Q3_GROSS_REVENUE_LABEL+'}),0,(${'+@Q3_GM_LABEL+'}/${'+@Q3_GROSS_REVENUE_LABEL+'}))'' AS [' + @Q3_GM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+ @Q4_GM_LABEL+'}/${'+@Q4_GROSS_REVENUE_LABEL+'}),0,(${'+@Q4_GM_LABEL+'}/${'+@Q4_GROSS_REVENUE_LABEL+'}))'' AS [' + @Q4_GM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+ @Q5_GM_LABEL+'}/${'+@Q5_GROSS_REVENUE_LABEL+'}),0,(${'+@Q5_GM_LABEL+'}/${'+@Q5_GROSS_REVENUE_LABEL+'}))'' AS [' + @Q5_GM_PER_LABEL + '],

			'+'''${'+ @Q1_NET_REVENUE_LABEL+'}-${'+@Q1_COGS_LABEL+'}'' AS [' + @Q1_NM_LABEL + '],
			'+'''${'+ @Q2_NET_REVENUE_LABEL+'}-${'+@Q2_COGS_LABEL+'}'' AS [' + @Q2_NM_LABEL + '],
			'+'''${'+ @Q3_NET_REVENUE_LABEL+'}-${'+@Q3_COGS_LABEL+'}'' AS [' + @Q3_NM_LABEL + '],
			'+'''${'+ @Q4_NET_REVENUE_LABEL+'}-${'+@Q4_COGS_LABEL+'}'' AS [' + @Q4_NM_LABEL + '],
			'+'''${'+ @Q5_NET_REVENUE_LABEL+'}-${'+@Q5_COGS_LABEL+'}'' AS [' + @Q5_NM_LABEL + '],

			'+'''IF(ISERROR(${'+@Q1_NM_LABEL+'}/${'+@Q1_NET_REVENUE_LABEL+'}),0,(${'+@Q1_NM_LABEL+'}/${'+@Q1_NET_REVENUE_LABEL+'}))'' AS [' + @Q1_NM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+@Q2_NM_LABEL+'}/${'+@Q2_NET_REVENUE_LABEL+'}),0,(${'+@Q2_NM_LABEL+'}/${'+@Q2_NET_REVENUE_LABEL+'}))'' AS [' + @Q2_NM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+@Q3_NM_LABEL+'}/${'+@Q3_NET_REVENUE_LABEL+'}),0,(${'+@Q3_NM_LABEL+'}/${'+@Q3_NET_REVENUE_LABEL+'}))'' AS [' + @Q3_NM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+@Q4_NM_LABEL+'}/${'+@Q4_NET_REVENUE_LABEL+'}),0,(${'+@Q4_NM_LABEL+'}/${'+@Q4_NET_REVENUE_LABEL+'}))'' AS [' + @Q4_NM_PER_LABEL + '],
			'+'''IF(ISERROR(${'+@Q5_NM_LABEL+'}/${'+@Q5_NET_REVENUE_LABEL+'}),0,(${'+@Q5_NM_LABEL+'}/${'+@Q5_NET_REVENUE_LABEL+'}))'' AS [' + @Q5_NM_PER_LABEL + ']
			'			
	END
	
	SET @QUERY = @QUERY + N', Q1_PORT_VOLUME AS [' + @Q1_PORT_VOLUME_LABEL + '],
			Q2_PORT_VOLUME AS [' + @Q2_PORT_VOLUME_LABEL + '],
			Q3_PORT_VOLUME AS [' + @Q3_PORT_VOLUME_LABEL + '],
			Q4_PORT_VOLUME AS [' + @Q4_PORT_VOLUME_LABEL + '],
			Q5_PORT_VOLUME AS [' + @Q5_PORT_VOLUME_LABEL + ']'
	
	SET  @QUERY = @QUERY + N' FROM #RESULTS_TABLE WHERE UPDATED = 0'
END
ELSE IF(@REPORT_TYPE = 'salesForecastTemplateGmChip')
BEGIN
	SET @QUERY = N'SELECT BU AS [BU],
		PLM AS [PLM],
		Product_Segment AS [Product Segment],
		Product_Family AS [Product Family],
		Chipset AS [Chipset],
		Sales_Person AS [Sales Person],
		Region AS [Region],
		Customer_Name AS [Customer Name],
		OEM AS [OEM] ,
		ODM AS [ODM],
		Carrier AS [Carrier],
		Chipset_Part_Name AS [Chipset Part Name],
		Marketing_Part_Number AS [Marketing Part Number],
		Q1_Volume AS [' + @Q1_VOLUME_LABEL + '],
		Q2_Volume AS [' + @Q2_VOLUME_LABEL + '],
		Q3_Volume AS [' + @Q3_VOLUME_LABEL + '],
		Q4_Volume AS [' + @Q4_VOLUME_LABEL + '],
		Q5_Volume AS [' + @Q5_VOLUME_LABEL + '],
		Q1_ASP AS [' + @Q1_ASP_LABEL + '],
		Q2_ASP AS [' + @Q2_ASP_LABEL + '],
		Q3_ASP AS [' + @Q3_ASP_LABEL + '],
		Q4_ASP AS [' + @Q4_ASP_LABEL + '],
		Q5_ASP AS [' + @Q5_ASP_LABEL + '],
		'+'''${'+@Q1_VOLUME_LABEL+'}*${'+@Q1_ASP_LABEL+'}'' AS [' + @Q1_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q2_VOLUME_LABEL+'}*${'+@Q2_ASP_LABEL+'}'' AS [' + @Q2_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q3_VOLUME_LABEL+'}*${'+@Q3_ASP_LABEL+'}'' AS [' + @Q3_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q4_VOLUME_LABEL+'}*${'+@Q4_ASP_LABEL+'}'' AS [' + @Q4_GROSS_REVENUE_LABEL + '],
		'+'''${'+@Q5_VOLUME_LABEL+'}*${'+@Q5_ASP_LABEL+'}'' AS [' + @Q5_GROSS_REVENUE_LABEL + '],
		Q1_Rebates AS [' + @Q1_REBATES_LABEL + '],
		Q2_Rebates AS [' + @Q2_REBATES_LABEL + '],
		Q3_Rebates AS [' + @Q3_REBATES_LABEL + '],
		Q4_Rebates AS [' + @Q4_REBATES_LABEL + '],
		Q5_Rebates AS [' + @Q5_REBATES_LABEL + '],
		
		'+'''${'+@Q1_ASP_LABEL+'}-${'+@Q1_REBATES_LABEL+'}'' AS [' + @Q1_NET_ASP_LABEL + '],
		'+'''${'+@Q2_ASP_LABEL+'}-${'+@Q2_REBATES_LABEL+'}'' AS [' + @Q2_NET_ASP_LABEL + '],
		'+'''${'+@Q3_ASP_LABEL+'}-${'+@Q3_REBATES_LABEL+'}'' AS [' + @Q3_NET_ASP_LABEL + '],
		'+'''${'+@Q4_ASP_LABEL+'}-${'+@Q4_REBATES_LABEL+'}'' AS [' + @Q4_NET_ASP_LABEL + '],
		'+'''${'+@Q5_ASP_LABEL+'}-${'+@Q5_REBATES_LABEL+'}'' AS [' + @Q5_NET_ASP_LABEL + '],

		'+'''${'+@Q1_VOLUME_LABEL+'}*${'+@Q1_NET_ASP_LABEL+'}'' AS [' + @Q1_NET_REVENUE_LABEL + '],
		'+'''${'+@Q2_VOLUME_LABEL+'}*${'+@Q2_NET_ASP_LABEL+'}'' AS [' + @Q2_NET_REVENUE_LABEL + '],
		'+'''${'+@Q3_VOLUME_LABEL+'}*${'+@Q3_NET_ASP_LABEL+'}'' AS [' + @Q3_NET_REVENUE_LABEL + '],
		'+'''${'+@Q4_VOLUME_LABEL+'}*${'+@Q4_NET_ASP_LABEL+'}'' AS [' + @Q4_NET_REVENUE_LABEL + '],		
		'+'''${'+@Q5_VOLUME_LABEL+'}*${'+@Q5_NET_ASP_LABEL+'}'' AS [' + @Q5_NET_REVENUE_LABEL + '],
		
		Q1_Material_Costs AS [' + @Q1_MAT_COST_LABEL + '],
		Q2_Material_Costs AS [' + @Q2_MAT_COST_LABEL + '],
		Q3_Material_Costs AS [' + @Q3_MAT_COST_LABEL + '],
		Q4_Material_Costs AS [' + @Q4_MAT_COST_LABEL + '],
		Q5_Material_Costs AS [' + @Q5_MAT_COST_LABEL + '],
		Q1_Royalty AS [' + @Q1_ROYALTY_LABEL + '],
		Q2_Royalty AS [' + @Q2_ROYALTY_LABEL + '],
		Q3_Royalty AS [' + @Q3_ROYALTY_LABEL + '],
		Q4_Royalty AS [' + @Q4_ROYALTY_LABEL + '],
		Q5_Royalty AS [' + @Q5_ROYALTY_LABEL + '],

		'+'''(${'+@Q1_MAT_COST_LABEL+'}+${'+@Q1_ROYALTY_LABEL+'})*0.05'' AS [' + @Q1_OH_LABEL + '],
		'+'''(${'+@Q2_MAT_COST_LABEL+'}+${'+@Q2_ROYALTY_LABEL+'})*0.05'' AS [' + @Q2_OH_LABEL + '],
		'+'''(${'+@Q3_MAT_COST_LABEL+'}+${'+@Q3_ROYALTY_LABEL+'})*0.05'' AS [' + @Q3_OH_LABEL + '],
		'+'''(${'+@Q4_MAT_COST_LABEL+'}+${'+@Q4_ROYALTY_LABEL+'})*0.05'' AS [' + @Q4_OH_LABEL + '],
		'+'''(${'+@Q5_MAT_COST_LABEL+'}+${'+@Q5_ROYALTY_LABEL+'})*0.05'' AS [' + @Q5_OH_LABEL + '],
		
		'+'''(${'+@Q1_MAT_COST_LABEL+'}+${'+@Q1_ROYALTY_LABEL+'}+${'+ @Q1_OH_LABEL +'})'' AS [' + @Q1_COGS_PER_UNIT_LABEL + '],
		'+'''(${'+@Q2_MAT_COST_LABEL+'}+${'+@Q2_ROYALTY_LABEL+'}+${'+ @Q2_OH_LABEL +'})'' AS [' + @Q2_COGS_PER_UNIT_LABEL + '],
		'+'''(${'+@Q3_MAT_COST_LABEL+'}+${'+@Q3_ROYALTY_LABEL+'}+${'+ @Q3_OH_LABEL +'})'' AS [' + @Q3_COGS_PER_UNIT_LABEL + '],
		'+'''(${'+@Q4_MAT_COST_LABEL+'}+${'+@Q4_ROYALTY_LABEL+'}+${'+ @Q4_OH_LABEL +'})'' AS [' + @Q4_COGS_PER_UNIT_LABEL + '],	
		'+'''(${'+@Q5_MAT_COST_LABEL+'}+${'+@Q5_ROYALTY_LABEL+'}+${'+ @Q5_OH_LABEL +'})'' AS [' + @Q5_COGS_PER_UNIT_LABEL + '],	

		'+'''${'+@Q1_COGS_PER_UNIT_LABEL+'}*${'+@Q1_VOLUME_LABEL+'}'' AS [' + @Q1_COGS_LABEL + '],
		'+'''${'+@Q2_COGS_PER_UNIT_LABEL+'}*${'+@Q2_VOLUME_LABEL+'}'' AS [' + @Q2_COGS_LABEL + '],
		'+'''${'+@Q3_COGS_PER_UNIT_LABEL+'}*${'+@Q3_VOLUME_LABEL+'}'' AS [' + @Q3_COGS_LABEL + '],
		'+'''${'+@Q4_COGS_PER_UNIT_LABEL+'}*${'+@Q4_VOLUME_LABEL+'}'' AS [' + @Q4_COGS_LABEL + '],
		'+'''${'+@Q5_COGS_PER_UNIT_LABEL+'}*${'+@Q5_VOLUME_LABEL+'}'' AS [' + @Q5_COGS_LABEL + '],

		'+'''${'+@Q1_GROSS_REVENUE_LABEL+'}-${'+@Q1_COGS_LABEL+'}'' AS [' + @Q1_GM_LABEL + '],
		'+'''${'+@Q2_GROSS_REVENUE_LABEL+'}-${'+@Q2_COGS_LABEL+'}'' AS [' + @Q2_GM_LABEL + '],
		'+'''${'+@Q3_GROSS_REVENUE_LABEL+'}-${'+@Q3_COGS_LABEL+'}'' AS [' + @Q3_GM_LABEL + '],
		'+'''${'+@Q4_GROSS_REVENUE_LABEL+'}-${'+@Q4_COGS_LABEL+'}'' AS [' + @Q4_GM_LABEL + '],
		'+'''${'+@Q5_GROSS_REVENUE_LABEL+'}-${'+@Q5_COGS_LABEL+'}'' AS [' + @Q5_GM_LABEL + '],

		'+'''${'+ @Q1_NET_REVENUE_LABEL+'}-${'+@Q1_COGS_LABEL+'}'' AS [' + @Q1_NM_LABEL + '],
		'+'''${'+ @Q2_NET_REVENUE_LABEL+'}-${'+@Q2_COGS_LABEL+'}'' AS [' + @Q2_NM_LABEL + '],
		'+'''${'+ @Q3_NET_REVENUE_LABEL+'}-${'+@Q3_COGS_LABEL+'}'' AS [' + @Q3_NM_LABEL + '],
		'+'''${'+ @Q4_NET_REVENUE_LABEL+'}-${'+@Q4_COGS_LABEL+'}'' AS [' + @Q4_NM_LABEL + '],
		'+'''${'+ @Q5_NET_REVENUE_LABEL+'}-${'+@Q5_COGS_LABEL+'}'' AS [' + @Q5_NM_LABEL + '],
		
		Q1_PORT_VOLUME AS [' + @Q1_PORT_VOLUME_LABEL + '],
		Q2_PORT_VOLUME AS [' + @Q2_PORT_VOLUME_LABEL + '],
		Q3_PORT_VOLUME AS [' + @Q3_PORT_VOLUME_LABEL + '],
		Q4_PORT_VOLUME AS [' + @Q4_PORT_VOLUME_LABEL + '],
		Q5_PORT_VOLUME AS [' + @Q5_PORT_VOLUME_LABEL + ']'

	SET  @QUERY = @QUERY + N' FROM #RESULTS_TABLE WHERE UPDATED = 0'
END
ELSE IF(@REPORT_TYPE = 'SALES' OR @REPORT_TYPE = 'OPERATIONS')
BEGIN
	SET @QUERY = N'SELECT Book AS [Book],
			BU AS [BU],
			PLM AS [PLM],
			Product_Segment AS [Product Segment],
			Product_Family AS [Product Family],
			Chipset AS [Chipset],
			Sales_Person AS [Sales Person],
			Region AS [Region],
			Customer_Name AS [Customer Name],
			OEM AS [OEM] ,
			ODM AS [ODM],
			Carrier AS [Carrier],
			Chipset_Part_Name AS [Chipset Part Name],
			Chipset_Description AS [Chipset Description],
			Marketing_Part_Number AS [Marketing Part Number],
			Manufacturing_Part_Number AS [Manufacturing Part Number],
			Status AS [Status],
			Chip_Type AS [Chip Type],
			Component_Description AS [Component Description],
			Port_Count AS [Port Count],
			chipset_component_count AS [chipset component count],
			package_size AS [package size],
			Package_Type AS [Package Type],
			Packing_Type AS [Packing Type],
			Packing_Quantity AS [Packing Quantity],
			Samples AS [Samples],
			Q1_Volume AS [' + @Q1_LABEL + ' Volume],
			Q2_Volume AS [' + @Q2_LABEL + ' Volume],
			Q3_Volume AS [' + @Q3_LABEL + ' Volume],
			Q4_Volume AS [' + @Q4_LABEL + ' Volume],
			Q5_Volume AS [' + @Q5_LABEL + ' Volume],
			Q1_ASP AS [' + @Q1_LABEL + ' ASP],
			Q2_ASP AS [' + @Q2_LABEL + ' ASP],
			Q3_ASP AS [' + @Q3_LABEL + ' ASP],
			Q4_ASP AS [' + @Q4_LABEL + ' ASP],
			Q5_ASP AS [' + @Q5_LABEL + ' ASP],
			Q1_Gross_Revenue AS [' + @Q1_LABEL + ' Gross Revenue],
			Q2_Gross_Revenue AS [' + @Q2_LABEL + ' Gross Revenue],
			Q3_Gross_Revenue AS [' + @Q3_LABEL + ' Gross Revenue],
			Q4_Gross_Revenue AS [' + @Q4_LABEL + ' Gross Revenue],
			Q5_Gross_Revenue AS [' + @Q5_LABEL + ' Gross Revenue],
			Q1_Rebates AS [' + @Q1_LABEL + ' Rebates],
			Q2_Rebates AS [' + @Q2_LABEL + ' Rebates],
			Q3_Rebates AS [' + @Q3_LABEL + ' Rebates],
			Q4_Rebates AS [' + @Q4_LABEL + ' Rebates],
			Q5_Rebates AS [' + @Q5_LABEL + ' Rebates],'

	IF(@REPORT_TYPE = 'OPERATIONS')
	BEGIN	
		SET @QUERY = @QUERY+ N'Q1_Net_Revenue AS [' + @Q1_LABEL + ' Net Revenue],
			Q2_Net_Revenue AS [' + @Q2_LABEL + ' Net Revenue],
			Q3_Net_Revenue AS [' + @Q3_LABEL + ' Net Revenue],
			Q4_Net_Revenue AS [' + @Q4_LABEL + ' Net Revenue],
			Q5_Net_Revenue AS [' + @Q5_LABEL + ' Net Revenue],
			Q1_Material_Costs AS [' + @Q1_LABEL + ' Material Costs],
			Q2_Material_Costs AS [' + @Q2_LABEL + ' Material Costs],
			Q3_Material_Costs AS [' + @Q3_LABEL + ' Material Costs],
			Q4_Material_Costs AS [' + @Q4_LABEL + ' Material Costs],
			Q5_Material_Costs AS [' + @Q5_LABEL + ' Material Costs],
			Q1_Royalty AS [' + @Q1_LABEL + ' Royalty],
			Q2_Royalty AS [' + @Q2_LABEL + ' Royalty],
			Q3_Royalty AS [' + @Q3_LABEL + ' Royalty],
			Q4_Royalty AS [' + @Q4_LABEL + ' Royalty],
			Q5_Royalty AS [' + @Q5_LABEL + ' Royalty],
			Q1_OH AS [' + @Q1_LABEL + ' OH],
			Q2_OH AS [' + @Q2_LABEL + ' OH],
			Q3_OH AS [' + @Q3_LABEL + ' OH],
			Q4_OH AS [' + @Q4_LABEL + ' OH],
			Q5_OH AS [' + @Q5_LABEL + ' OH],
			Q1_COGS AS [' + @Q1_LABEL + ' COGS],
			Q2_COGS AS [' + @Q2_LABEL + ' COGS],
			Q3_COGS AS [' + @Q3_LABEL + ' COGS],
			Q4_COGS AS [' + @Q4_LABEL + ' COGS],
			Q5_COGS AS [' + @Q5_LABEL + ' COGS],
			Q1_GM AS [' + @Q1_LABEL + ' GM],
			Q2_GM AS [' + @Q2_LABEL + ' GM],
			Q3_GM AS [' + @Q3_LABEL + ' GM],
			Q4_GM AS [' + @Q4_LABEL + ' GM],
			Q5_GM AS [' + @Q5_LABEL + ' GM],
			Q1_GM_PCTG AS [' + @Q1_LABEL + ' GM%],
			Q2_GM_PCTG AS [' + @Q2_LABEL + ' GM%],
			Q3_GM_PCTG AS [' + @Q3_LABEL + ' GM%],
			Q4_GM_PCTG AS [' + @Q4_LABEL + ' GM%],
			Q5_GM_PCTG AS [' + @Q5_LABEL + ' GM%],'
	END

	SET @QUERY = @QUERY+ N'Q1_SHIPPED_TO_DATE,
			Q1_Scheduled_BKLG,
			Q2_Scheduled_BKLG,
			Unscheduled_BKLG
		
	 FROM #RESULTS_TABLE WHERE UPDATED = 0'
END
		
--PRINT '@QUERY=' + @QUERY
EXEC (@QUERY)

END